package com.ck.easy.service.impl;

import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
import com.ck.common.utils.file.FileUtils;
import com.ck.easy.common.entity.SourceEntity;
import com.ck.easy.excel.EasyExcelUtil;
import com.ck.easy.factory.EasyExcelWriterFactory;
import com.ck.easy.mapper.EasyExcelMapper;
import com.ck.easy.service.EasyExcelService;
import com.ck.easy.utils.JDBCDruidUtils;
import com.github.pagehelper.PageHelper;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Timestamp;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

/**
 * @author ck
 * @date 2023/8/25 15:58
 */

/**
 * 实现思路：
 * 首先在查询数据库层面，需要分批进行查询（每次查询20w）
 * 每查询一次结束，就使用EasyExcel工具将这些数据写入一次；
 * 当一个Sheet写满了100w条数据，开始将查询的数据写入到另一个Sheet中；
 * 如此循环直到数据全部导出到Excel完毕。
 */
@Service
@Slf4j
public class EasyExcelServiceImpl implements EasyExcelService {

    @Autowired
    EasyExcelMapper easyExcelMapper;

    @Value("${file.exportPath}")
    String filePath;

    String defaultFileName = "学生成绩表.xlsx";

    @Override
    // 分批次导出百万级数据
    public void exportFile(HttpServletResponse response) throws IOException {
        //1、获取总数据
        Integer totalCount = easyExcelMapper.selectSource();

        //每一个Sheet存放100w条数据
        Integer sheetDataRows = 1000000;

        //每次写入的数据量20w
        Integer writeDataRows = 200000;

        //计算需要的sheet数量
        Integer sheetNum = totalCount % sheetDataRows == 0 ? totalCount / sheetDataRows : totalCount / sheetDataRows + 1;

        //计算一般情况下每一个sheet需要写入的次数(一般情况不包含最后一个sheet,因为最后一个sheet不确定会写入多少条数据)
        Integer oneSheetWriteCount = sheetDataRows / writeDataRows;

        //计算最后一个sheet需要写入的次数
        Integer lastSheetWriteCount = totalCount % sheetDataRows == 0 ? oneSheetWriteCount : (totalCount % sheetDataRows % writeDataRows == 0 ? (totalCount / sheetDataRows / writeDataRows) : (totalCount / sheetDataRows / writeDataRows + 1));

        String realFileName = generateUniqueFileName();
        String fileSavePath = filePath + realFileName;
        FileUtils.setAttachmentResponseHeader(response, realFileName);
        EasyExcelWriterFactory factory = EasyExcelUtil.writeWithSheets(fileSavePath);

        //开始分批查询分次写入
        for (int i = 0; i < sheetNum; i++) {
            //循环写入的次数
            int writeCount = (i != sheetNum - 1) ? oneSheetWriteCount : lastSheetWriteCount;
            for (int j = 0; j < writeCount; j++) {
                //分页查询一次20w
                PageHelper.startPage(j + 1 + oneSheetWriteCount * i, writeDataRows);
                List<SourceEntity> reslultList = easyExcelMapper.selectByPage20w();
                if (!CollectionUtils.isEmpty(reslultList)) {
                    try {
                        factory.writeModel(SourceEntity.class, reslultList, "学习分数表" + i);
                    } catch (Exception e) {
                        throw new RuntimeException(e);
                    }
                }
            }
        }
        factory.finish();
        log.info("导出文件保存路径>>>>{}", fileSavePath);
    }


    /**
     * 分批读取(20w一次)+JDBC分批插入+手动事务控制
     * JDBC批量插入性能 > MyBatis批量插入性能
     *【Mybatis批量插入和JDBC批量插入性能对比 https://www.cnblogs.com/wxw7blog/p/8706797.html】
     * @param dataList
     * @return
     */
    @Override
    public Map<String, Object> importFile(List<SourceEntity> dataList) {
        HashMap<String, Object> result = new HashMap<>();
        //结果集中数据为0时,结束方法.进行下一次调用
        if (dataList.size() == 0) {
            result.put("empty", "0000");
            return result;
        }
        //JDBC分批插入+事务操作完成对20w数据的插入
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            long startTime = System.currentTimeMillis();
            System.out.println(dataList.size() + "条,开始导入到数据库时间:" + startTime + "ms");
            conn = JDBCDruidUtils.getConnection();
            //控制事务:默认不提交
            conn.setAutoCommit(false);
            String sql = "insert into source_table (id,senior,name,chinaese,english,math,comprehensive,gender,height,create_date) values";
            sql += "(?,?,?,?,?,?,?,?,?,?)";
            ps = conn.prepareStatement(sql);
            //循环结果集:这里循环不支持"烂布袋"表达式
            for (int i = 0; i < dataList.size(); i++) {
                SourceEntity source = dataList.get(i);
                ps.setString(1, source.getId().toString());
                ps.setString(2, source.getSenior().toString());
                ps.setString(3, source.getName());
                ps.setString(4, source.getChinaese().toString());
                ps.setString(5, source.getEnglish().toString());
                ps.setString(6, source.getMath().toString());
                ps.setString(7, source.getComprehensive().toString());
                ps.setString(8, source.getGender().toString());
                ps.setString(9, source.getHeight().toString());
                ps.setTimestamp(10, new Timestamp(System.currentTimeMillis()));
                //将一组参数添加到此 PreparedStatement 对象的批处理命令中。
                ps.addBatch();
            }
            //执行批处理
            ps.executeBatch();
            //手动提交事务
            conn.commit();
            long endTime = System.currentTimeMillis();
            System.out.println(dataList.size() + "条,结束导入到数据库时间:" + endTime + "ms");
            System.out.println(dataList.size() + "条,导入用时:" + (endTime - startTime) + "ms");
            result.put("success", "1111");
        } catch (Exception e) {
            result.put("exception", "0000");
            e.printStackTrace();
        } finally {
            //关连接
            JDBCDruidUtils.close(conn, ps);
        }
        return result;
    }


    private String generateUniqueFileName() {
        return System.currentTimeMillis() + "_" + UUID.randomUUID().toString() + defaultFileName;
    }
}
